<?php
namespace app\admin\controller;
header("content-type:text/html;charset=utf-8");
use app\admin\controller\Base;
use app\admin\model\Admin;
use app\admin\model\Apply;
use app\admin\model\Applytype;
use app\admin\model\Classtype;
use app\admin\model\Course;
use app\admin\model\Coursetype;
use app\admin\model\Dustbin;
use app\admin\model\Examtype;
use app\admin\model\Myclass;
use app\admin\model\Naturetype;
use app\admin\model\Teacher;
use app\admin\model\User;
use app\admin\model\Usertype;
use think\App;
use think\facade\Db;
use think\Request;
use think\File;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Session;

class  Applys extends Base
{
  /*
   * 所有记录列表
   */
  public function applylist(Request $request)
  {
    $user = $this->user_info();
    //查询所有的报名分类，
    $resource = collection(Applytype::order("sort asc")->select())->toArray();
    //给查询每个分类下的所有资讯
    foreach ($resource as $k => $v) {
      $newslist = Apply::where(array("type" => $v['id']))->order("id desc")->select();
//                ->paginate(1,false,[
//                'path'=> "/admin/Applys/applylist/typeid/".$v['id'],
//                'var_page' => 'page'.$v['id'],
//            ]);
//      var_dump(collection($newslist)->toArray());
      foreach ($newslist as $ks => $vs) {
        $type = Applytype::where(array('id' => $vs['type']))->find();
        //查询对应的用户
        $thisUser = User::where(["id"=>$vs['userid'],"grade"=>date("Y")])->find();
        if(!empty($thisUser)) {
          $newslist[$ks]['userinfo'] = $thisUser;
          $newslist[$ks]['typetext'] = $type['type_name'];
        }else{
          unset($newslist[$ks]);
        }
      }

      $resource[$k]['newslist'] = collection($newslist)->toArray();
      $resource[$k]['length'] = count($newslist);
    }
    $typeid = !empty($request->route("typeid")) ? $request->route("typeid") : $resource[0]['id'];
    $data = array(
      "setting" => $this->getAllSetting(),
      "activeid" => $typeid,
      "list" => $resource,
      "teacher"=>collection(Teacher::where("status",1)->order("name asc")->select())->toArray(),
    );
    return view("lists", $data);
  }
  //上传文件
  public function upload(Request $request)
  {
    //接收上传的文件
    $file = $this->request->file('file');
    if (!empty($file)) {
      //图片存的路径
      $imgUrl = ROOT_PATH . 'public' . DS . 'static' . DS . 'admin' . DS . 'uploads' . DS . 'news';
      // 移动到框架应用根目录/public/uploads/ 目录下
      $info = $file->validate(['size' => 1048576, 'ext' => 'jpg,png,gif'])->rule('uniqid')->move($imgUrl);
      $error = $file->getError();
      //验证文件后缀后大小
      if (!empty($error)) {
        dump($error);
        exit;
      }
      if ($info) {
        // 成功上传后 获取上传信息
        //获取图片的名字
        $imgName = $info->getFilename();
        //获取图片的路径
        $photo = $imgName;

      } else {
        // 上传失败获取错误信息
        $file->getError();
      }
    } else {
      $photo = '';
    }
    if ($photo !== '') {
      return ['code' => 1, 'msg' => '成功', 'photo' => $photo];
    } else {
      return ['code' => 404, 'msg' => '失败'];
    }
  }
  //添加
  public function addform()
  {
    $user = $this->user_info();
    $data = array(
      "setting" => $this->getAllSetting(),
      "list" => Coursetype::select(),
    );
    return view("addform", $data);
  }
  public function add(Request $request)
  {
    $user = $this->user_info();
    $data = $request->post();
    $data['create_time'] = time();
    $data['update_time'] = time();
    $res = Course::insert($data);
    if ($res) {
      $this->success("添加成功", "Applys/applylist?typeid=" . $request->post('type'));
    } else {
      $this->error("添加失败", "Applys/addform");
    }
  }
  //修改
  public function update(Request $request)
  {
    $user = $this->user_info();
    $id = $request->get("id");
    $apply = Apply::where(array("id" => $id))->find(); //查询当前的报名信息
    $data = array(
      "setting" => $this->getAllSetting(),
      "status" => Applytype::select(),
      "data" => $apply,
      "user" => User::where(array("id" => $apply['userid']))->find(),
    );
    return view("update", $data);
  }
  public function toUpdate(Request $request)
  {
    $user = $this->user_info();
    $data = $request->post();
    $naturetext = Naturetype::where(array("id" => $data['natrueid']))->find();
    $data['natrue'] = $naturetext['type_name'];
    $coursetext = Course::where(array("id" => $data['courseid']))->find();
    $data['course'] = $coursetext['name'];
    $data['update_time'] = time();
    if (Apply::update($data)) {
      $this->success("修改成功", "Applys/applylist?typeid=" . $request->post('type'));
    } else {
      $this->error("修改失败");
    }

  }
  //报名记录删除
  public function deletes(Request $request)
  {
      $user = $this->user_info();
      $apply = Apply::get($request->post('id'));
      $user = User::get($apply['userid']);
      $del_comment = $request->post("del_comment");
      $arr = array(
        "major"=>$apply['major'],
        "bktype"=>$apply['bktype'],
        "apply_create_time"=>strtotime($apply['create_time']),
        "apply_update_time"=>strtotime($apply['update_time']),
        "reason"=>$apply['reason'],
        "comment"=>$apply['comment'],
        "status"=>$apply['status'],
        "paytype"=>$apply['paytype'],
        "payid"=>$apply['payid'],
        "type"=>$apply['type'],
        "pay_time"=>$apply['pay_time'],
        "major2"=>$apply['major2'],
        "isadjust"=>$apply['isadjust'],
        "teacher"=>$apply['teacher'],
        "advice_note"=>$apply['advice_note'],
        "grade"=>$apply['grade'],
        "teacher_comment"=>$apply['teacher_comment'],
        "teacher_id"=>$apply['teacher_id'],
        "major_id"=>$apply['major_id'],
        "major2_id"=>$apply['major2_id'],
        "bktype_id"=>$apply['bktype_id'],
        "coursetype_name"=>$apply['coursetype_name'],
        "coursetype_id"=>$apply['coursetype_id'],
        "name"=>$user['name'],
        "pwd"=>$user['pwd'],
        "sex"=>$user['sex'],
        "phone"=>$user['phone'],
        "idcard"=>$user['idcard'],
        "user_create_time"=>strtotime($user['create_time']),
        "user_update_time"=>strtotime($user['update_time']),
        "nation"=>$user['nation'],
        "qqwx"=>$user['qqwx'],
        "parenttel"=>$user['parenttel'],
        "schooladdress"=>$user['schooladdress'],
        "schoolname"=>$user['schoolname'],
        "score"=>$user['score'],
        "address"=>$user['address'],
        "del_time"=>time(),
        "del_comment"=>$del_comment,
      );
      if (Dustbin::create($arr)) {
        User::destroy($apply['userid']);
        Apply::destroy($request->post('id'));
        Session::delete("user_infos");
        $this->success("已移入回收站", "Applys/applylist?typeid=" . $request->post('typeid'));
      } else {
        $this->error("移入回收站失败", "Applys/applylist?typeid=" . $request->post('typeid'));
      }
  }
  // 修改为待支付
  public function dzf(Request $request)
  {
    $data = $request->post();
    $data['status'] = 4;
    $data['type'] = 4;
    $res = Apply::update($data);
    if ($res) {
      $this->success("修改成功", "Applys/applylist?typeid=" . $request->post('typeid'));
    } else {
      $this->error("修改失败");
    }
  }
  //------------------------------------------------------------------------------------------------------------------
  //分类列表
  public function type_list()
  {
    $user = $this->user_info();
    $resource = collection(Applytype::order("sort asc")->select())->toArray();
    foreach ($resource as $k => $v) {
      $sum = Apply::where(array("type" => $v['id']))->count();
      $resource[$k]['sum'] = $sum;
    }

    $data = array(
      "setting" => $this->getAllSetting(),
      "list" => $resource,
    );
    return view("typelist", $data);
  }
  //添加
  public function type_addform()
  {
    $user = $this->user_info();
    $data = array(
      "setting" => $this->getAllSetting(),
    );
    return view("type_addform", $data);
  }
  public function type_add(Request $request)
  {
    //接收所有的请求数据，数组：多个数据的集合
    $data = $request->post();
    // 创建数组元素
    $data['create_time'] = time();
    $data['update_time'] = time();
    $res = Applytype::insert($data);  //执行数据库命令
    //返回结果的判断
    if ($res) {
      $this->success("添加成功", "Applys/type_list");
    } else {
      $this->error("添加失败", "Applys/type_addform");
    }
  }
  //删除分类
  public function type_deletes(Request $request)
  {
    $user = $this->user_info();
    $id = $request->post('id');
    //删除分类的时先去新闻标中查看该分类下是否有新闻，如果有则不允许删除
    $sum = Apply::where(array("type" => $id))->count();
    if ($sum > 0) {
      $this->error("该分类有报名记录，请将报名记录修改到其他分类或删除该分类下的所有报名记录之后再删除。");
      exit();
    }
    //如果该分类下没有新闻则直接删除
    if (Applytype::destroy($id)) {
      $this->success("删除成功", "Applys/type_list");
    } else {
      $this->error("删除失败");
    }
  }
  //修改分类状态
  public function setTypeStatus(Request $request)
  {
    $user = $this->user_info();
    if (Applytype::update($request->post())) {
      $this->success("成功", "Applys/type_list");
    } else {
      $this->error("失败");
    }
  }
  //修改分类
  public function type_update(Request $request)
  {
    $user = $this->user_info();
    $id = $request->get("id");
    $data = array(
      "setting" => $this->getAllSetting(),
      "data" => Applytype::where(array("id" => $id))->find(),
    );
    return view("type_update", $data);
  }
  //执行修改分类
  public function type_toUpdate(Request $request)
  {
    $user = $this->user_info();
    $data = $request->post();
    $data['update_time'] = time();
    if (Applytype::update($data)) {
      $this->success("修改成功", "Applys/type_list");
    } else {
      $this->error("修改失败");
    }

  }
  //修改报名记录为拒绝状态
  public function times(Request $request)
  {
    $user = $this->user_info();
    $data = $request->post();
    $data['status'] = 3;
    $data['type'] = 3;
    if (Apply::update($data)) {
      $this->success("修改成功", "Applys/applylist?typeid=" . $data['type']);
    } else {
      $this->error("修改失败");
    }
  }
  //修改报名记录为通过状态
  public function check(Request $request)
  {
    $user = $this->user_info();
    $data = $request->post();
    $data['status'] = 2;
    $data['type'] = 2;
    if (Apply::update($data)) {
      $this->success("修改成功", "Applys/applylist?typeid=" . $data['type']);
    } else {
      $this->error("修改失败");
    }
  }
  //查询课程类型下的所有未毕业班级
  public function selectClass(Request $request)
  {
//        $data  = $request->post();
    $data["id"] = 2;
    $class = Classtype::where(array("course" => ['like', "%" . $data['id'] . "%"]))->select();

    $allClass = array();
    foreach ($class as $ks => $vs) {
      var_dump($vs);
      $myclass = Myclass::where(array("type" => ['like', "%" . $vs['id'] . "%"]))->select();
      foreach ($myclass as $key => $val) {
        $allClass[] = array("id" => $val['id'], "status" => $val['status'], "classid" => $val['classid']);
      }
    }
    $str = "";
    if (!empty($allClass)) {
      foreach ($allClass as $ks => $vs) {
        $status = "";
        switch ($vs['status']) {
          case 1 :
            $status = "待上课";
            break;
          case 2 :
            $status = "上课中";
            break;
        }
        $str .= "<option value='" . $vs['classid'] . "'>【" . $status . "】 " . $vs['classid'] . "</option>";
      }
    } else {
      $str .= "<option value=''>未找到可选班级</option>";
    }
    $this->success($str);
  }
  //修改学生为已录取
  public function classAddStudent(Request $request)
  {
    $setting = $this->getAllSetting();
    $data = $request->post();
    $data['type'] = 2;
    $data['status'] = 2;
    $data['update_time'] = time();
    if (Apply::update($data)) {
      $apply = Apply::get($data['id']);
      $student = User::get($apply['userid']);
      $res = message($student['phone'],["name"=>$student['name'],"major"=>$apply['major']],3); //发送录取通知短信
      //生成二维码图片
      $path = $setting['weburl']."public/index.php/home/my/tzs/id/".$apply['id'];
      $tzs_name =  $this->qr_code_dir($path,"student_tzs");
      Apply::update(['id'=>$data['id'],"tzs_img"=>$tzs_name]);
      $msg_msg ="录取成功";
      if($res['stat'] == 100){
        $msg_msg .= ",短信发送：".$res['message'];
      }else{
        $msg_msg .= "，短信发送失败：".$res['message'];
      }
      $this->success($msg_msg, "Applys/applylist");
    } else {
      $this->error("录取成功");
    }
  }
  //修改学生为报名审核中
  public function classAddStudent2(Request $request)
  {
    $data = $request->post();
    $data['type'] = 4;
    $data['status'] = 4;
    if (Apply::update($data)) {
      $apply = Apply::get($data['id']);
      $student = User::get($apply['userid']);
      $res = message($student['phone'],["name"=>$student['name'],"major"=>$apply['major']],2); //后台审核信息录取成功
      $msg_msg ="报名成功";
      if($res['stat'] == 100){
        $msg_msg .= ",短信发送：".$res['message'];
      }else{
        $msg_msg .= ",短信发送失败：".$res['message'];
      }
      $this->success($msg_msg, "Applys/applylist");
    } else {
      $this->error("报名失败");
    }
  }
  //修改学生的专业
  public function update_major(Request $request)
  {
    $user = $this->user_info();
    $res = Apply::get($request->get("id"));
    $data = array(
      "pid" => $request->get("pid"),
      "apply" => $res,
      "user" => user::get($res['userid']),
      "teacher"=>Teacher::where('status',1)->select(),//老师没有被禁用的老师
      "examtype" => Examtype::select(),
      "setting" => $this->getAllSetting(),
    );
    return view("update_major", $data);
  }
  //修改学生专业时查询专业大类
  public function select_coursetype(Request $request){
      //接收报考类型
        $data = $request->post();
        //查询专业大类
        $res = Coursetype::where("examtype_id",$data['examtype_id'])->select();
        if($res){
            return json(collection($res)->toArray());
        }else{
            return json([]);
        }
    }
  //修改学生专业时查询专业
  public function select_major(Request $request){
  //接收专业大类id
     $data = $request->post();
     //查询专业
     $res = Course::where("typeid",$data['coursetype_id'])->select();
     if($res){
         return json(collection($res)->toArray());
     }else{
         return json([]);
     }
  }
  //完成修改学生的专业
  public function save_update_major(Request $request)
  {
    $data = $request->post();
    $pid = $data["pid"];
    unset($data["pid"]);
    if (Apply::update($data)) {
      $this->success("修改成功", "Applys/applylist?typeid={$pid}");
    } else {
      $this->error("修改失败");
    }
  }
  //录取学生导出Excel表格
  public function lqExcel()
  {
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    //设置工作表标题名称
    $worksheet->setTitle('录取学生名单');
    //表头
    //设置单元格内容
    $worksheet->setCellValueByColumnAndRow(1, 1, '录取学生名单');
    $worksheet->setCellValueByColumnAndRow(1, 2, '日期');
    $worksheet->setCellValueByColumnAndRow(2, 2, '学生姓名');
    $worksheet->setCellValueByColumnAndRow(3, 2, '性别');
    $worksheet->setCellValueByColumnAndRow(4, 2, '民族');
    $worksheet->setCellValueByColumnAndRow(5, 2, '身份证号');
    $worksheet->setCellValueByColumnAndRow(6, 2, '学生电话');
    $worksheet->setCellValueByColumnAndRow(7, 2, '家长姓名');
    $worksheet->setCellValueByColumnAndRow(8, 2, '家长电话');
    $worksheet->setCellValueByColumnAndRow(9, 2, '家庭住址');
    $worksheet->setCellValueByColumnAndRow(10, 2, '报名专业');
    $worksheet->setCellValueByColumnAndRow(11, 2, '毕业学校');
    $worksheet->setCellValueByColumnAndRow(12, 2, '中考分数');
    $worksheet->setCellValueByColumnAndRow(13, 2, '职业升学指导老师');
    $worksheet->setCellValueByColumnAndRow(14, 2, '备注');
    //合并单元格
    $worksheet->mergeCells('A1:N1');

    $styleArray = [
      'font' => [
        'bold' => true
      ],
      'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];
    //设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(20);
    $worksheet->getStyle('A2:N2')->applyFromArray($styleArray)->getFont()->setSize(14);
    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
    $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(75);
    $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('k')->setWidth(35);
    $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(30);
    $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(28);

    //查询所有录取信息
    $newslist = collection(Apply::where(array("type" => 2,'grade'=>date('Y',time())))->order("userid asc")->select())->toArray();
    foreach ($newslist as $key => $v) {
      $newslist[$key]['user'] = User::get($v['userid']);
    }
    $len = count($newslist);
    $j = 0;
    for ($i = 0; $i < $len; $i++) {
      $j = $i + 3; //从表格第3行开始
      if ($newslist[$i]['user']['sex']) $sex = "男"; else $sex = "女";
      $worksheet->setCellValueByColumnAndRow(1, $j, $newslist[$i]['create_time']);
      $worksheet->setCellValueByColumnAndRow(2, $j, $newslist[$i]['user']['name']);
      $worksheet->setCellValueByColumnAndRow(3, $j, $sex);
      $worksheet->setCellValueByColumnAndRow(4, $j, $newslist[$i]['user']['nation']);
      $worksheet->setCellValueByColumnAndRow(5, $j, $newslist[$i]['user']['idcard'] . "\t");
      $worksheet->setCellValueByColumnAndRow(6, $j, $newslist[$i]['user']['phone']);
      $worksheet->setCellValueByColumnAndRow(7, $j, "");
      $worksheet->setCellValueByColumnAndRow(8, $j, $newslist[$i]['user']['parenttel']);
      $worksheet->setCellValueByColumnAndRow(9, $j, $newslist[$i]['user']['address']);
      $worksheet->setCellValueByColumnAndRow(10, $j, $newslist[$i]['major']);
      $worksheet->setCellValueByColumnAndRow(11, $j, $newslist[$i]['user']['schoolname']);
      $worksheet->setCellValueByColumnAndRow(12, $j, $newslist[$i]['user']['score']);
      $worksheet->setCellValueByColumnAndRow(13, $j, $newslist[$i]['teacher']);
      $worksheet->setCellValueByColumnAndRow(14, $j, "");

    }
    //设置表格样式
    $styleArrayBody = [
      'borders' => [
        'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '666666'],
        ],
      ],
      'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];

    $styleArrayBodys = [
      'borders' => [
        'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '666666'],
        ],
      ],
    ];
    $total_jzInfo = $len + 2;
    //添加所有边框/居中
    $worksheet->getStyle('A2:N' . $total_jzInfo)->applyFromArray($styleArrayBody);

    $filename = '录取学生名单' . date("Y-m-d(His)") . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }
  //待支付学生导出Excel表格
  public function dzfExcel()
  {
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    //设置工作表标题名称
    $worksheet->setTitle('待支付学生名单');

    //表头
    //设置单元格内容
    $worksheet->setCellValueByColumnAndRow(1, 1, '待支付名单');
    $worksheet->setCellValueByColumnAndRow(1, 2, '学生ID');
    $worksheet->setCellValueByColumnAndRow(2, 2, '姓名');
    $worksheet->setCellValueByColumnAndRow(3, 2, '性别');
    $worksheet->setCellValueByColumnAndRow(4, 2, '身份证号');
    $worksheet->setCellValueByColumnAndRow(5, 2, '学生电话');
    $worksheet->setCellValueByColumnAndRow(6, 2, '父母电话');
    $worksheet->setCellValueByColumnAndRow(7, 2, '报考类型');
    $worksheet->setCellValueByColumnAndRow(8, 2, '专业大类');
    $worksheet->setCellValueByColumnAndRow(9, 2, '专业1');
    $worksheet->setCellValueByColumnAndRow(10, 2, '专业2');
    $worksheet->setCellValueByColumnAndRow(11, 2, '负责老师');
    $worksheet->setCellValueByColumnAndRow(12, 2, '通讯地址');
    $worksheet->setCellValueByColumnAndRow(13, 2, '通知书编号');

    //合并单元格
    $worksheet->mergeCells('A1:M1');

    $styleArray = [
      'font' => [
        'bold' => true
      ],
      'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];
    //设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(20);

    $worksheet->getStyle('A2:M2')->applyFromArray($styleArray)->getFont()->setSize(14);
    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(27);
    $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(14);
    $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(75);
    $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);



    //查询所有录取信息
    $newslist = collection(Apply::where(array("type" => 4,'grade'=>date('Y',time())))->order("userid asc")->select())->toArray();
    foreach ($newslist as $key => $v) {
      $newslist[$key]['user'] = User::get($v['userid']);
    }
    $len = count($newslist);
    $j = 0;
    for ($i = 0; $i < $len; $i++) {
      $j = $i + 3; //从表格第3行开始
      $worksheet->setCellValueByColumnAndRow(1, $j, $newslist[$i]['user']['id']);
      $worksheet->setCellValueByColumnAndRow(2, $j, $newslist[$i]['user']['name']);
      if ($newslist[$i]['user']['sex']) $sex = "男"; else $sex = "女";
      $worksheet->setCellValueByColumnAndRow(3, $j, $sex);
      $worksheet->setCellValueByColumnAndRow(4, $j, $newslist[$i]['user']['idcard'] . "\t");
      $worksheet->setCellValueByColumnAndRow(5, $j, $newslist[$i]['user']['phone']);
      $worksheet->setCellValueByColumnAndRow(6, $j, $newslist[$i]['user']['parenttel']);
      $worksheet->setCellValueByColumnAndRow(7, $j, $newslist[$i]['bktype']);
      $worksheet->setCellValueByColumnAndRow(8, $j, $newslist[$i]['coursetype_name']);
      $worksheet->setCellValueByColumnAndRow(9, $j, $newslist[$i]['major']);
      $worksheet->setCellValueByColumnAndRow(10, $j, $newslist[$i]['major2']);
      $worksheet->setCellValueByColumnAndRow(11, $j, $newslist[$i]['teacher']);
      $worksheet->setCellValueByColumnAndRow(12, $j, $newslist[$i]['user']['address']);
      $worksheet->setCellValueByColumnAndRow(13, $j, $newslist[$i]['advice_note']);


    }
    //表格样式
    $styleArrayBody = [
      'borders' => [
        'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '666666'],
        ],
      ],
      'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];

    $styleArrayBodys = [
      'borders' => [
        'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '666666'],
        ],
      ],
    ];
    $total_jzInfo = $len + 2;
    //添加所有边框/居中
    $worksheet->getStyle('A2:M' . $total_jzInfo)->applyFromArray($styleArrayBody);

    $filename = '待支付学生名单' . date("Y-m-d(His)") . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }
  //生成图片
  public function create_advice(Request $request)
  {
    $setting = $this->getAllSetting();
    header('content-type:image/png');
    $user = $this->user_info();
    //查询用户信息和用户的报名信息
    $student = User::get($request->get("id"));
    $coursetype = collection(coursetype::all())->toArray();
    $ctype = [];
    foreach ($coursetype as $key=>$val){
      $ctype[$val['id']] = $val['name'];
    }

//    $student = User::get(213);
    $sex = $student['sex'] == 0 ? "女" : "男";
//    $student_apply = Apply::where(["userid"=>$request->get('id')])->find();
    $as = \Think\Db::query("select apply.id,apply.tzs_img,apply.major,course.name,coursetype.name as typename from apply,course,coursetype where apply.userid = ".$request->get('id')." and course.id = apply.major_id and coursetype.id = course.typeid");
    if(empty($as[0]['tzs_img'])){
      //生成二维码图片
      $path = $setting['weburl']."public/index.php/home/my/tzs/id/".$as[0]['id'];
      $tzs_name =  $this->qr_code_dir($path,"student_tzs");
      Apply::update(['id'=>$as[0]['id'],"tzs_img"=>$tzs_name]);
      $as = \Think\Db::query("select apply.id,apply.tzs_img,apply.major,course.name,coursetype.name as typename from apply,course,coursetype where apply.userid = ".$request->get('id')." and course.id = apply.major_id and coursetype.id = course.typeid");
    }
    //通知书二维码路径
    $code_url = $setting['weburl']."public/static/admin/uploads/student_tzs/".$as[0]["tzs_img"];
    $dst = imagecreatefrompng ($code_url); //读取二维码
    $src_imageType = getimagesize($code_url); //获取二维码信息
    $imginfo = [];
    $imginfo['width'] = $src_imageType[0]; //二维码宽度
    $imginfo['height'] = $src_imageType[1]; //二维码高度

//    $student_apply = Apply::where(["userid"=>	213])->find();
    $apply_node = $request->get("time");
    $width =2551;
    $height =3437;
    $img =imagecreatetruecolor($width,$height);
    $color = imagecolorallocate($img,255,255,255);
    $textcolor = imagecolorallocate($img,0,0,0);
    imagefill($img,0,0,$color);
//    imagestring($img,57,434,1225,$apply_node,$textcolor);
    imagettftext($img, 64, 0, 275, 1080, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $apply_node);
    imagettftext($img, 60, 0, 130, 1285, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $student['name']);
    imagettftext($img, 60, 0, 1784, 1424, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $as[0]['typename']);
    imagettftext($img, 65, 0, 328, 1587, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", "三");

    imagettftext($img, 54, 0, 1284, 2352, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("Y")));
    imagettftext($img, 54, 0, 1774, 2352, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("n")));
    imagettftext($img, 54, 0, 1990, 2352, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("d")));

    imagettftext($img, 64, 0, 2106, 2943, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $apply_node);
    imagettftext($img, 60, 0, 379, 3116, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $student['name']);
    imagettftext($img, 60, 0, 1478, 3116, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $as[0]['typename']);
    imagettftext($img, 60, 0, 379, 3270, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $sex);
    imagettftext($img, 60, 0, 1478, 3270, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", "三");

    imagettftext($img, 54, 0, 1581, 3370, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("Y")));
    imagettftext($img, 54, 0, 2083, 3370, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("n")));
    imagettftext($img, 54, 0, 2283, 3370, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("d")));
    imagecopyresampled ($img,$dst,$width/2-$imginfo['width']/2+34,1080-300,0,0,$imginfo['width'],$imginfo['height'],$imginfo['width'],$imginfo['height']);
    imagepng($img);
    imagedestroy($img);
    exit();
  }
  public function create_advice2(Request $request)
  {
    header('content-type:image/png');
    $user = $this->user_info();
    //查询用户信息和用户的报名信息
    $student = User::get($request->get("id"));
    $coursetype = collection(coursetype::all())->toArray();
    $ctype = [];
    foreach ($coursetype as $key=>$val){
      $ctype[$val['id']] = $val['name'];
    }

//    $student = User::get(213);
    $sex = $student['sex'] == 0 ? "女" : "男";
//    $student_apply = Apply::where(["userid"=>$request->get('id')])->find();
    $as = \Think\Db::query("select apply.major,course.name,coursetype.name as typename from apply,course,coursetype where apply.userid = ".$request->get('id')." and course.name = apply.major2 and coursetype.id = course.typeid");

//    $student_apply = Apply::where(["userid"=>	213])->find();
    $apply_node = $request->get("time");
    $width =2480;
    $height =3508;
    $img =imagecreatetruecolor($width,$height);
    $color = imagecolorallocate($img,255,255,255);
    $textcolor = imagecolorallocate($img,0,0,0);
    imagefill($img,0,0,$color);
//    imagestring($img,57,434,1225,$apply_node,$textcolor);
    imagettftext($img, 64, 0, 404, 1150, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $apply_node);
    imagettftext($img, 60, 0, 233, 1298, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $student['name']);
    imagettftext($img, 60, 0, 1704, 1444, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $as[0]['typename']);
    imagettftext($img, 65, 0, 458, 1587, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", "三");

    imagettftext($img, 64, 0, 2006, 2774, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $apply_node);
    imagettftext($img, 60, 0, 379, 2945, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $student['name']);
    imagettftext($img, 60, 0, 1478, 2945, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $as[0]['typename']);
    imagettftext($img, 60, 0, 379, 3071, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $sex);
    imagettftext($img, 60, 0, 1478, 3071, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", "三");

    imagettftext($img, 54, 0, 1451, 3170, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("Y")));
    imagettftext($img, 54, 0, 1913, 3170, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("n")));
    imagettftext($img, 54, 0, 2123, 3170, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $this->big_number(date("d")));
    imagepng($img);
    imagedestroy($img);
    exit();
  }
  //将年月日转为汉字
  public function big_number($number){
    $str = "";
    $len = strlen($number);
    if($len == 4) {
      for ($i = 0; $i < $len; $i++) {
        switch (substr($number, $i, 1)) {
          case 1 :
            $str .= "一";
            break;
          case 2 :
            $str .= "二";
            break;
          case 3 :
            $str .= "三";
            break;
          case 4 :
            $str .= "四";
            break;
          case 5 :
            $str .= "五";
            break;
          case 6 :
            $str .= "六";
            break;
          case 7 :
            $str .= "七";
            break;
          case 8 :
            $str .= "八";
            break;
          case 9 :
            $str .= "九";
            break;
          case 0 :
            $str .= "〇";
            break;
        }
      }
    }else if($len == 2 ){
      switch (substr($number, 0, 1)) {
        case 1 :
          $str .= "十";
          break;
        case 2 :
          $str .= "二十";
          break;
        case 3 :
          $str .= "三十";
          break;
      }
      switch (substr($number, 1, 1)) {
        case 1 :
          $str .= "一";
          break;
        case 2 :
          $str .= "二";
          break;
        case 3 :
          $str .= "三";
          break;
        case 4 :
          $str .= "四";
          break;
        case 5 :
          $str .= "五";
          break;
        case 6 :
          $str .= "六";
          break;
        case 7 :
          $str .= "七";
          break;
        case 8 :
          $str .= "八";
          break;
        case 9 :
          $str .= "九";
          break;
        case 0 :
          $str .= "";
          break;
      }
    }else if($len == 1 ){
      switch ($number) {
        case 1 :
          $str .= "一";
          break;
        case 2 :
          $str .= "二";
          break;
        case 3 :
          $str .= "三";
          break;
        case 4 :
          $str .= "四";
          break;
        case 5 :
          $str .= "五";
          break;
        case 6 :
          $str .= "六";
          break;
        case 7 :
          $str .= "七";
          break;
        case 8 :
          $str .= "八";
          break;
        case 9 :
          $str .= "九";
          break;
      }
    }
    return $str;
  }
  //确认已经width: 50%了通知书
  public function advice_node(Request $request){
    $data = $request->post();
    $pid = $data["pid"];
    unset($data["pid"]);
    //将通知书编号保存
    if (Apply::update($data)) {
      $this->success("通知书编号保存成功", "Applys/applylist?typeid={$pid}");
    } else {
      $this->error("通知书编号保存失败");
    }

  }
}